package org.firebug.spring.boot.jpa.repository.dao;

import org.firebug.spring.boot.jpa.repository.entity.UserEntity;
import org.firebug.spring.boot.jpa.repository.qo.UserQo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.Collection;
import java.util.List;

@Repository
public interface UserDao extends JpaRepository<UserEntity, Integer>, JpaSpecificationExecutor<UserEntity> {
    List<UserEntity> findBy();      // select * from t_user;
    List<UserEntity> findFirstBy(); // select * from t_user limit 1;
    List<UserEntity> findTopBy();   // select * from t_user limit 1;
    List<UserEntity> findTop5By();  // select * from t_user limit 5;
    List<UserEntity> findDistinctBy();      // select distinct * from t_user;
    List<UserEntity> findDistinctFirstBy(); // select distinct * from t_user limit 1;
    List<UserEntity> findDistinctTopBy();   // select distinct * from t_user limit 1;
    List<UserEntity> findDistinctTop5By();  // select distinct * from t_user limit 5;

    List<UserEntity> findByOrderByName();     // order by name asc(默认：asc)
    List<UserEntity> findByOrderByNameAsc();  // order by name asc
    List<UserEntity> findByOrderByNameDesc(); // order by name desc
    List<UserEntity> findByOrderByNameAscGenderDesc();   // order by name asc, gender desc

    List<UserEntity> findByIdIsNull();     //id is null
    List<UserEntity> findByIdNotNull();    //id is not null
    List<UserEntity> findByIdIsNotNull();  //id is not null

    List<UserEntity> findById(int id);                 //id=?
    List<UserEntity> findByIdNot(int id);              //id<>?
    List<UserEntity> findByIdLessThan(int id);         //id<?
    List<UserEntity> findByIdLessThanEqual(int id);    //id<=?
    List<UserEntity> findByIdGreaterThan(int id);      //id>?
    List<UserEntity> findByIdGreaterThanEqual(int id); //id>=?

    List<UserEntity> findByIdIn(int... id);                 //id in (? , ?)
    List<UserEntity> findByIdIn(Collection<Integer> id);    //id in (? , ?)
    List<UserEntity> findByIdNotIn(int... id);              //id not in (? , ?)
    List<UserEntity> findByIdNotIn(Collection<Integer> id); //id not in (? , ?)

    List<UserEntity> findByNameLike(String name);         //name like %?%
    List<UserEntity> findByNameStartingWith(String name); //name like  ?%
    List<UserEntity> findByNameEndingWith(String name);   //name like %?
    List<UserEntity> findByNameNotLike(String name);      //name not like %?%
    List<UserEntity> findByNameIgnoreCase(String name);   //upper(name)=upper(?)

    List<UserEntity> findByDeletedTrue();  //deleted=1
    List<UserEntity> findByDeletedFalse(); //deleted=0

    List<UserEntity> findByCreateAtBefore(Timestamp end);        //create_at<?
    List<UserEntity> findByCreateAtLessThan(Timestamp end);      //create_at<?
    List<UserEntity> findByCreateAtLessThanEqual(Timestamp end); //create_at<=?
    List<UserEntity> findByCreateAtAfter(Timestamp start);            //create_at>?
    List<UserEntity> findByCreateAtGreaterThan(Timestamp start);      //create_at<?
    List<UserEntity> findByCreateAtGreaterThanEqual(Timestamp start); //create_at<=?
    List<UserEntity> findByCreateAtBetween(Timestamp start, Timestamp end);                                  //create_at between {start} and {end}
    List<UserEntity> findByCreateAtGreaterThanAndCreateAtLessThan(Timestamp start, Timestamp end);           //create_at>{start}  and create_at<{end}
    List<UserEntity> findByCreateAtGreaterThanEqualAndCreateAtLessThanEqual(Timestamp start, Timestamp end); //create_at>={start} and create_at<={end}
    List<UserEntity> findByCreateAtLessThanAndUpdateAtGreaterThan(Timestamp now1, Timestamp now2);           //create_at<{now1}  and update_at>{now2}
    List<UserEntity> findByCreateAtLessThanEqualAndUpdateAtGreaterThanEqual(Timestamp now1, Timestamp now2); //create_at<={now1} and update_at>={now2}

    @Query(value = "select coalesce(sum(salary),0) from t_user where id=:id", nativeQuery = true)
    BigDecimal sumSalaryById(@Param("id") int id);

    @Query(value = "select coalesce(min(salary),0) from t_user where id=:id", nativeQuery = true)
    BigDecimal minSalaryById(@Param("id") int id);

    @Query(value = "select coalesce(max(salary),0) from t_user where id=:id", nativeQuery = true)
    BigDecimal maxSalaryById(@Param("id") int id);

    @Query(value = "select coalesce(avg(salary),0) from t_user where id=:id", nativeQuery = true)
    BigDecimal avgSalaryById(@Param("id") int id);

    /** ?1 */
    @Query(value = "select * from t_user where id=?1", nativeQuery = true)
    UserEntity queryById(int id);
//    @Query(value = "select * from t_user where id=:id", nativeQuery = true)
//    UserEntity queryById(@Param("id") int id);

    /** :name */
    @Query(value = "select * from t_user where name like %:name%", nativeQuery = true)
//    @Query(value = "select * from t_user where name like concat('%',:name,'%')", nativeQuery = true)
    List<UserEntity> queryByName(@Param("name") String name);

    @Query(value = "select * from t_user where id=:#{#qo.id} and name=:#{#qo.name}", nativeQuery = true)
    List<UserEntity> queryByQo(@Param("qo") UserQo qo);

    @Query(value = "select * from t_user where 1=1 and if(:id is null,1=1,id=:id) and if(:name='',1=1,name=:name)", nativeQuery = true)
    List<UserEntity> queryByIdName(@Param("id") Integer id, @Param("name") String name);

    @Modifying
    @Query(value = "update t_user set salary=:salary where id=:id", nativeQuery = true)
    int modifySalaryById(@Param("id") int id, @Param("salary") BigDecimal salary);

    @Modifying
    @Query(value = "delete from t_user where id=:id", nativeQuery = true)
    void deleteById(@Param("id") int id);
}